﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using KlauserswagModel;

namespace WebShopDAL
{
    public class CategoryDB
    {
        //Create:
        /// <summary>
        /// Method for inserting a new Category.
        /// </summary>
        /// <param name="category">The Category to be inserted to the Database.</param>
        /// <returns>Returns 1 if everything went ok.</returns>
        public static int InsertNewCategory(Category category)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Categories.Add(category);
                return context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in CategoryDB, InsertNewCategory");

                //    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[Categories]([CategoryName]) ");
                //    sqlQuery.Append("VALUES(@CategoryName)");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("CategoryName", SqlDbType.NChar).Value = category.CategoryName;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in CategoryDB, InsertNewCategory", ex);
            }
        }

        //Read:
        /// <summary>
        /// Method for retrieving a Category based on ID.
        /// </summary>
        /// <param name="categoryId">The ID of the category to get.</param>
        /// <returns>Returns a category object containg all category information</returns>
        public static Category GetCategory(int categoryId)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Categories.Where(c => c.CategoryID == categoryId).ToList();

                var category = new Category();

                foreach (var cat in query)
                {
                    category = cat;
                }
                return category;

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in CategoryDB, GetCategory");

                //    var sqlQuery = new StringBuilder("SELECT [CategoryID],[CategoryName]");
                //    sqlQuery.Append("FROM [dbo].[Categories]");
                //    sqlQuery.Append("WHERE [CategoryID] = @CategoryID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("CategoryID", SqlDbType.BigInt).Value = categoryId;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool categoryFound = dataSet.Tables[0].Rows.Count == 1;

                //        if (categoryFound)
                //        {
                //            return new Category(categoryName: dataSet.Tables[0].Rows[0]["CategoryName"].ToString().Trim(),
                //                categoryId: categoryId);
                //        }
                //        else
                //        {
                //            return null;
                //        }
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in CategoryDB, GetCategory", ex);
            }
        }

        /// <summary>
        /// Method for retrieving all categories stored in the Database.
        /// </summary>
        /// <returns>Returns all Categories found in Database</returns>
        public static List<Category> GetAllCategories()
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Categories.ToList();
                return query;

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in CategoryDB, GetAllCategories");

                //    var sqlQuery = new StringBuilder("SELECT * FROM [dbo].[Categories]");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool categoryFound = dataSet.Tables[0].Rows.Count > 0;

                //        if (!categoryFound)
                //            return null;

                //        var categories = new List<Category>();

                //        for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                //        {
                //            categories.Add(new Category(
                //                categoryName: dataSet.Tables[0].Rows[i]["CategoryName"].ToString().Trim(),
                //                categoryId: int.Parse(dataSet.Tables[0].Rows[i]["CategoryID"].ToString())
                //                ));
                //        }
                //        return categories;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in CategoryDB, GetAllCategories", ex);
            }
        }

        //Update:
        /// <summary>
        /// Method for updating a Category.
        /// </summary>
        /// <param name="category">The category you want to update</param>
        /// <returns>Returns 1 if everything went ok.</returns>
        public static int UpdateCategory(Category category)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Categories.Where(c => c.CategoryID == category.CategoryID);
                return context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in CategoryDB, UpdateCategory");

                //    var sqlQuery = new StringBuilder("UPDATE [dbo].[Categories] ");
                //    sqlQuery.Append("SET [CategoryName] = @CategoryName ");
                //    sqlQuery.Append("WHERE [CategoryID] = @CategoryID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("CategoryID", SqlDbType.NChar).Value = category.CategoryId;
                //        command.Parameters.Add("CategoryName", SqlDbType.NChar).Value = category.CategoryName;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in CategoryDB, UpdateCategory", ex);
            }
        }

        //Delete:
        /// <summary>
        /// Method for deleting a categroy on a specific ID.
        /// </summary>
        /// <param name="categoryId">The ID of the category to delete</param>
        /// <returns>Returns 1 if everything went ok.</returns>
        public static int DeleteCategory(int categoryId)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Categories.Where(c => c.CategoryID == categoryId);
                return context.SaveChanges();
                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in CategoryDB, DeleteCategory");

                //    var sqlQuery = new StringBuilder("DELETE FROM [dbo].[Categories] WHERE [CategoryID] = @CategoryID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("CategoryID", SqlDbType.BigInt).Value = categoryId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in CategoryDB, DeleteCategory", ex);
            }
        }
    }
}
